package com.cn.sys.frame.util;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

import com.cn.sys.common.Constants;
import com.cn.sys.main.util.TableColumnUtil;

public class BaseDaoUitl {

	/**
	 * 获取插入语句，把实体名称转换成表名称(实体SysUser转换成表SYS_USER)，然后根据表名称获取字段，组装insert语句
	 * 类似insert into(ID,USER_NAME,NAME) SYS_USER values(:id,:userName,:name),
	 * @return
	 */
	public static String getInsertSql(Class entityClass){
		/*PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(entityClass);
		if (pds==null||pds.length<=0) {
			throw new IllegalArgumentException("错误的实体,实体的属性个数为0.");
		}
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("INSERT INTO ").append(underscoreName(entityClass.getSimpleName()).toUpperCase()).append("(");
		StringBuffer valuebBuffer = new StringBuffer();
		valuebBuffer.append("VALUES(");
		for (PropertyDescriptor pd : pds) {
			if (pd.getWriteMethod() != null) {
				String fieldName = pd.getName();
				String underscoredName = underscoreName(fieldName);
				sqlBuffer.append(underscoredName.toUpperCase()).append(",");
				valuebBuffer.append(":").append(fieldName).append(",");
			}
		}
		valuebBuffer.deleteCharAt(valuebBuffer.length()-1).append(")");
		sqlBuffer.deleteCharAt(sqlBuffer.length()-1).append(") ").append(valuebBuffer);
		return sqlBuffer.toString();*/
		String tableName = underscoreName(entityClass.getSimpleName()).toUpperCase();
		List<String> columns = TableColumnUtil.get(tableName);
		if(columns==null||columns.isEmpty()){
			throw new IllegalArgumentException(tableName+"表不存在!");
		}
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("INSERT INTO ").append(tableName).append(" (");
		StringBuffer valuebBuffer = new StringBuffer();
		valuebBuffer.append("VALUES (");
		for (String col : columns) {
			if(checkSysDbKeyIncreament()){
				if(StringUtils.equalsIgnoreCase(col, "ID")){
					continue;
				}
			}
			sqlBuffer.append(col).append(" ,");
			valuebBuffer.append(":").append(convertUnderscoreNameToPropertyName(col)).append(",");
		}
		valuebBuffer.deleteCharAt(valuebBuffer.length()-1).append(")");
		sqlBuffer.deleteCharAt(sqlBuffer.length()-1).append(") ").append(valuebBuffer);
		return sqlBuffer.toString();
	}
	/**
	 * 获取更新语句
	 * @return
	 */
	public static String getUpdateSql(Class entityClass){
		/*PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(entityClass);
		if (pds==null||pds.length<=0) {
			throw new IllegalArgumentException("错误的实体,实体的属性个数为0.");
		}
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("UPDATE ").append(underscoreName(entityClass.getSimpleName()).toUpperCase()).append(" SET ");
		for (PropertyDescriptor pd : pds) {
			if (pd.getWriteMethod() != null) {
				String fieldName = pd.getName();
				if(!StringUtils.equalsIgnoreCase(fieldName, "id")){
					String underscoredName = underscoreName(fieldName);
					sqlBuffer.append(underscoredName.toUpperCase()).append(" = :").append(fieldName).append(",");
				}
			}
		}
		sqlBuffer.deleteCharAt(sqlBuffer.length()-1);
		return sqlBuffer.toString();*/
		
		String tableName = underscoreName(entityClass.getSimpleName()).toUpperCase();
		List<String> columns = TableColumnUtil.get(tableName);
		if(columns==null||columns.isEmpty()){
			throw new IllegalArgumentException(tableName+"表不存在!");
		}
		if(columns.size()==1){
			throw new IllegalArgumentException(tableName+"表只有一个字段无法进行更新操作!");
		}
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("UPDATE ").append(tableName).append(" SET ");
		for (String col : columns) {
			if(!StringUtils.equalsIgnoreCase(col, "ID")){
				sqlBuffer.append(col).append(" = :").append(convertUnderscoreNameToPropertyName(col)).append(",");
			}
		}
		sqlBuffer.deleteCharAt(sqlBuffer.length()-1);
		return sqlBuffer.toString();
	}
	/**
	 * 根据实体类获取删除语句,类似DELETE FROM SYS_USER
	 * @return
	 */
	public static String getDeleteSql(Class entityClass){
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("DELETE FROM ").append(underscoreName(entityClass.getSimpleName()).toUpperCase());
		return sqlBuffer.toString();
	}
	/**
	 * 根据实体类获取查询语句,类似SELECT ID,USER_NAME,PASS_WORD,NAME FROM SYS_USER
	 * @return
	 */
	public static String getSelectSql(Class entityClass){
		/*PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(entityClass);
		if (pds==null||pds.length<=0) {
			throw new IllegalArgumentException("错误的实体,实体的属性个数为0.");
		}
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("SELECT ");
		for (PropertyDescriptor pd : pds) {
			if (pd.getWriteMethod() != null) {
				String underscoredName = underscoreName(pd.getName());
				sqlBuffer.append(underscoredName.toUpperCase()).append(",");
			}
		}
		sqlBuffer.deleteCharAt(sqlBuffer.length()-1).append(underscoreName(entityClass.getSimpleName()).toUpperCase());
		return sqlBuffer.toString();*/
		String tableName = underscoreName(entityClass.getSimpleName()).toUpperCase();
		List<String> columns = TableColumnUtil.get(tableName);
		if(columns==null||columns.isEmpty()){
			throw new IllegalArgumentException(tableName+"表不存在!");
		}
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("SELECT ");
		for (String col : columns) {
			sqlBuffer.append(col).append(" ,");
		}
		sqlBuffer.deleteCharAt(sqlBuffer.length()-1).append("FROM ").append(tableName).append(" WHERE 1=1");
		return sqlBuffer.toString();
	}
	/**
	 * 获取实体类的统计语句
	 * @return
	 */
	public static String getEntityCountSql(Class entityClass){
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("SELECT COUNT(*) FROM ").append(underscoreName(entityClass.getSimpleName()).toUpperCase()).append(" WHERE 1=1");
		return sqlBuffer.toString();
	}
	/**
	 * 获取SQL语句的统计语句
	 * @param sql
	 * @return
	 */
	public static String getCountSql(String sql){
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("SELECT COUNT(*) FROM (").append(sql).append(") CNT_TEMP_");
		return sqlBuffer.toString();
	}
	/**
	 * 字符串转换  userName --->  USER_NAME
	 * 把实体类名称转换成表名称,类似SysUser----->SYS_USER
	 * @param name
	 * @return
	 */
	public static String underscoreName(String name) {
		StringBuilder result = new StringBuilder();
		if (name != null && name.length() > 0) {
			result.append(name.substring(0, 1).toLowerCase());
			for (int i = 1; i < name.length(); i++) {
				String s = name.substring(i, i + 1);
				if (s.equals(s.toUpperCase())) {
					result.append("_");
					result.append(s.toLowerCase());
				}
				else {
					result.append(s);
				}
			}
		}
		return result.toString();
	}
	
	/**
	 * 字符串转换   USER_NAME  --->  userName  
	 *           userName  ---->  username
	 * 把字段名称转换成实体类对应的属性名称 ID--->id,USER_NAME---->userName
	 * @param name
	 * @return
	 */
	public static String convertUnderscoreNameToPropertyName(String name) {
		StringBuilder result = new StringBuilder();
		boolean nextIsUpper = false;
		if (name != null && name.length() > 0) {
			if (name.length() > 1 && name.substring(1,2).equals("_")) {
				result.append(name.substring(0, 1).toUpperCase());
			}
			else {
				result.append(name.substring(0, 1).toLowerCase());
			}
			for (int i = 1; i < name.length(); i++) {
				String s = name.substring(i, i + 1);
				if (s.equals("_")) {
					nextIsUpper = true;
				}
				else {
					if (nextIsUpper) {
						result.append(s.toUpperCase());
						nextIsUpper = false;
					}
					else {
						result.append(s.toLowerCase());
					}
				}
			}
		}
		return result.toString();
	}
	
	public static boolean checkSysDbKeySequence(){
		return Constants.ORACLE_DB.equalsIgnoreCase(Constants.SYS_DB_TYPE)
				||Constants.POSTGRESQL_DB.equalsIgnoreCase(Constants.SYS_DB_TYPE);
	}
	public static boolean checkSysDbKeyIncreament(){
		return Constants.MYSQL_DB.equalsIgnoreCase(Constants.SYS_DB_TYPE)
				||Constants.SQLSERVER_DB.equalsIgnoreCase(Constants.SYS_DB_TYPE);
	}
}
